﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

IF object_id(N'dbo.UDS_WorkAttendence_DaySetting', 'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[UDS_WorkAttendence_DaySetting] (
        [datetime] datetime NOT NULL,
        [DayValue] as (dbo.fn_GetDayValue([datetime])) PERSISTED,
        [WeekDay]  int      NULL,
        [NeedDuty] bit      NOT NULL CONSTRAINT [DF_UDS_WorkAttendence_DaySetting_needduty] default((1)),
        [ID]       int      NOT NULL identity(1, 1),

        CONSTRAINT [PK_UDS_WA_DaySetting] PRIMARY KEY NONCLUSTERED ([id] asc)
    );
    CREATE UNIQUE CLUSTERED INDEX [IX_UDS_WorkAttendence_DaySetting_DayValue] ON dbo.UDS_WorkAttendence_DaySetting([DayValue] asc);
END
GO

SET NOCOUNT ON
IF NOT EXISTS (SELECT 1 FROM dbo.[UDS_WorkAttendence_DaySetting])
BEGIN
    DECLARE @date datetime;
    DECLARE @endDate datetime;
    DECLARE @weekDay int;

    SET DATEFIRST 1; -- Sets the first day of a week as Monday for this session!
    SET @date = '2000-01-01';
    SET @endDate = '2020-12-31';

    WHILE ( @date <= @endDate )
    BEGIN
        SET @weekDay = datepart(weekday, @date);
        IF ( @weekDay >= 6 )
        BEGIN
            -- weekends
            INSERT INTO dbo.[UDS_WorkAttendence_DaySetting]([datetime], NeedDuty) VALUES(@date, 0);
        END
        ELSE
        BEGIN
            -- Working days
            INSERT INTO dbo.[UDS_WorkAttendence_DaySetting]([datetime], NeedDuty) VALUES(@date, 1);
        END
    
        SET @date = dateadd(day, 1, @date);
    END;
END
GO